package dth.dao;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;

import dth.entity.PaymentHistory;
import dth.entity.User;
import dth.entity.UserPackage;

public class UserDAO {
	private SessionFactory sessionFactory=new AnnotationConfiguration().configure().buildSessionFactory();
	private Session session=sessionFactory.openSession();
	
	public java.util.List<User> getListUser(){
		//session.beginTransaction(); //remove khi map many to many
		String queryString="select * from user";
		Query query=session.createSQLQuery(queryString).addEntity(User.class);
		return query.list();
	}
	public int delete(int id)
	{
		Transaction tx=session.beginTransaction();
		String queryString="delete from user where customer_id= :id";
		Query query=session.createSQLQuery(queryString).setParameter("id", id);
		int count=query.executeUpdate();
		tx.commit();
		return count;
	}
	public int insert(User pack)
	{
		SessionFactory sessionFactory=new AnnotationConfiguration().configure().buildSessionFactory();
		Transaction tx=session.beginTransaction();
		session.save(pack);
		tx.commit();
		return 1;
	}
	public int update(User pack)
	{
		
		SessionFactory sessionFactory=new AnnotationConfiguration().configure().buildSessionFactory();
		Transaction tx=session.beginTransaction();
		session.update(pack);
		tx.commit();
		return 1;
	}
	/*
	 * so tien phai tra hang thang
	 */
	public double getPriceChargePerMonth(int userId){
		String queryString="SELECT SUM(p.package_price) from user_package  k, package p WHERE k.fk_user_id=:id and k.fk_package=p.package_id;";		
		Object c=((Double)session.createSQLQuery(queryString).setParameter("id",userId).uniqueResult()).doubleValue();
		return Double.parseDouble(c.toString());
	}
	/*
	 * lay so thang chua thanh toan
	 */
	public int getNumMonthNoCharge(int userId){
		int months=0;
		PaymentHistoryDAO dao=new PaymentHistoryDAO();
		List<PaymentHistory> listHistories=dao.getListPaymentHistoryByUserId(userId);
		/*
		 * neu chua thanh toan lan nao thi tim kay ngay dat package
		 */
		if(listHistories==null||listHistories.size()==0)
		{
			UserPackageDAO userPackageDAO=new UserPackageDAO();
			List<UserPackage> listUserPackages=userPackageDAO.getListUserPackgeByUserId(userId);
			if(listUserPackages==null || listUserPackages.size()==0)
				return 0;
			else
			{
				java.util.Date laDate=new java.util.Date(listUserPackages.get(0).getDateInsert().getTime());
				Date toDate=new Date();
				if(toDate.getYear()==laDate.getYear())
					months= toDate.getMonth()-laDate.getMonth();
				else
				{
					months=toDate.getMonth()+12*(toDate.getYear()-laDate.getYear())-laDate.getMonth();
				}
			}
		}
		else{
			PaymentHistory lastPaHistory=listHistories.get(listHistories.size()-1);
			java.util.Date laDate=new java.util.Date(lastPaHistory.getDateInsert().getTime());
			Date toDate=new Date();
			if(toDate.getYear()==laDate.getYear())
				months= toDate.getMonth()-laDate.getMonth();
			else
			{
				months=toDate.getMonth()+12*(toDate.getYear()-laDate.getYear())-laDate.getMonth();
			}
		}
		if(months<=0)
			months=0;
		return months;
	}
	/*
	 * check user by customer id & carid
	 */
	public User checkUserByCusIDandCardId(int cusId,String cardId){
		String queryString="select * from user where customer_id=:cusId and card_number=:cardId";
		Query query=session.createSQLQuery(queryString).addEntity(User.class);
		query.setParameter("cusId", cusId);
		query.setParameter("cardId",cardId);
		List<User> listUsers=query.list();
		if(listUsers==null||listUsers.size()==0)
			return null;
		return listUsers.get(0);
	}
	public User checkUserByCusID(int cusId){
		String queryString="select * from user where customer_id=:cusId";
		Query query=session.createSQLQuery(queryString).addEntity(User.class);
		query.setParameter("cusId", cusId);
		List<User> listUsers=query.list();
		if(listUsers==null||listUsers.size()==0)
			return null;
		return listUsers.get(0);
	}
	public User checkUserByCardId(String cardId){
		String queryString="select * from user where card_number=:cardId";
		Query query=session.createSQLQuery(queryString).addEntity(User.class);
		query.setParameter("cardId",cardId);
		List<User> listUsers=query.list();
		if(listUsers==null||listUsers.size()==0)
			return null;
		return listUsers.get(0);
	}
	/*
	 * lay thong tin user
	 */
	public List<User> getUserByID(int userId){
		String queryString="select * from user where customer_id=:id";
		Query query=session.createSQLQuery(queryString).addEntity(User.class);
		query.setParameter("id",userId);
		List<User> users=query.list();
		return users;
	}
	
}
